﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using fcwms.Common;

namespace fcwms.DAL
{
    /// <summary>
    /// 数据访问类:dt_location
    /// </summary>
    public partial class dt_location
    {
        public dt_location()
        { }
        #region  BasicMethod
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from dt_location");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 是否存在库位
        /// </summary>
        /// <param name="position"></param>
        /// <returns></returns>
        public bool Exists(string position)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from dt_location");
            strSql.Append(" where position=@position");
            SqlParameter[] parameters = {
					new SqlParameter("@position", SqlDbType.NVarChar,50)
			};
            parameters[0].Value = position;
            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(fcwms.Model.dt_location model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into dt_location(");
            strSql.Append("decla_id,position,storage_id,shelf_id,floor_id,line_id,is_exist)");
            strSql.Append(" values (");
            strSql.Append("@decla_id,@position,@storage_id,@shelf_id,@floor_id,@line_id,@is_exist)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@decla_id", SqlDbType.NVarChar,100),
                    new SqlParameter("@position", SqlDbType.NVarChar,50),
					new SqlParameter("@storage_id", SqlDbType.NVarChar,50),
					new SqlParameter("@shelf_id", SqlDbType.NVarChar,50),
					new SqlParameter("@floor_id", SqlDbType.NVarChar,50),
					new SqlParameter("@line_id", SqlDbType.NVarChar,50),
					new SqlParameter("@is_exist", SqlDbType.Int,4)};
            parameters[0].Value = model.decla_id;
            parameters[1].Value = model.position;
            parameters[2].Value = model.storage_id;
            parameters[3].Value = model.shelf_id;
            parameters[4].Value = model.floor_id;
            parameters[5].Value = model.line_id;
            parameters[6].Value = model.is_exist;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(fcwms.Model.dt_location model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update dt_location set ");
            strSql.Append("decla_id=@decla_id,");
            strSql.Append("position=@position,");
            strSql.Append("storage_id=@storage_id,");
            strSql.Append("shelf_id=@shelf_id,");
            strSql.Append("floor_id=@floor_id,");
            strSql.Append("line_id=@line_id,");
            strSql.Append("is_exist=@is_exist");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@decla_id", SqlDbType.NVarChar,100),
                    new SqlParameter("@position", SqlDbType.NVarChar,50),
					new SqlParameter("@storage_id", SqlDbType.NVarChar,50),
					new SqlParameter("@shelf_id", SqlDbType.NVarChar,50),
					new SqlParameter("@floor_id", SqlDbType.NVarChar,50),
					new SqlParameter("@line_id", SqlDbType.NVarChar,50),
					new SqlParameter("@is_exist", SqlDbType.Int,4),
					new SqlParameter("@id", SqlDbType.Int,4)};
            parameters[0].Value = model.decla_id;
            parameters[1].Value = model.position;
            parameters[2].Value = model.storage_id;
            parameters[3].Value = model.shelf_id;
            parameters[4].Value = model.floor_id;
            parameters[5].Value = model.line_id;
            parameters[6].Value = model.is_exist;
            parameters[7].Value = model.id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from dt_location ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from dt_location ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 查找不存在的菜单并删除
        /// </summary>
        public void DeleteList2(SqlConnection conn, SqlTransaction trans, List<Model.dt_location> models, string decla_id)
        {
            StringBuilder idList = new StringBuilder();
            if (models != null)
            {
                foreach (Model.dt_location modelt in models)
                {
                    if (modelt.id > 0)
                    {
                        idList.Append(modelt.id + ",");
                    }
                }
            }
            string id_list = Utils.DelLastChar(idList.ToString(), ",");
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from dt_location where decla_id=" + decla_id);
            if (!string.IsNullOrEmpty(id_list))
            {
                strSql.Append(" and id not in(" + id_list + ")");
            }
            DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString());
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public fcwms.Model.dt_location GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 id,decla_id,position,storage_id,shelf_id,floor_id,line_id,is_exist from dt_location ");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
					new SqlParameter("@id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;

            fcwms.Model.dt_location model = new fcwms.Model.dt_location();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public fcwms.Model.dt_location DataRowToModel(DataRow row)
        {
            fcwms.Model.dt_location model = new fcwms.Model.dt_location();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["decla_id"] != null)
                {
                    model.decla_id = row["decla_id"].ToString();
                }
                if (row["position"] != null)
                {
                    model.position = row["position"].ToString();
                }
                if (row["storage_id"] != null)
                {
                    model.storage_id = row["storage_id"].ToString();
                }
                if (row["shelf_id"] != null)
                {
                    model.shelf_id = row["shelf_id"].ToString();
                }
                if (row["floor_id"] != null)
                {
                    model.floor_id = row["floor_id"].ToString();
                }
                if (row["line_id"] != null)
                {
                    model.line_id = row["line_id"].ToString();
                }
                if (row["is_exist"] != null && row["is_exist"].ToString() != "")
                {
                    model.is_exist = int.Parse(row["is_exist"].ToString());
                }
            }
            return model;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public fcwms.Model.dt_location GetModel2(string decla_id, string storage_id, string shelf_id)
        {
            fcwms.Model.dt_location model = new fcwms.Model.dt_location();

            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("select id,decla_id,position,storage_id,shelf_id,floor_id,line_id from dt_location ");
            strSql2.Append(" where decla_id=@decla_id and storage_id=@storage_id and shelf_id=@shelf_id  ");
            SqlParameter[] parameters2 = {
					new SqlParameter("@decla_id", SqlDbType.NVarChar,100),
                    new SqlParameter("@storage_id",SqlDbType.NVarChar,50),
                    new SqlParameter("@shelf_id",SqlDbType.NVarChar,50)};
            parameters2[0].Value = decla_id;
            parameters2[1].Value = storage_id;
            parameters2[2].Value = shelf_id;


            DataSet ds2 = DbHelperSQL.Query(strSql2.ToString(), parameters2);
            if (ds2.Tables[0].Rows.Count > 0)
            {
                int i = ds2.Tables[0].Rows.Count;
                List<fcwms.Model.dt_location> models = new List<fcwms.Model.dt_location>();
                 fcwms.Model.dt_location modelt;
                for (int n = 0; n < i; n++)
                {
                    modelt = new fcwms.Model.dt_location();
                    if (ds2.Tables[0].Rows[n]["id"].ToString() != "")
                    {
                        modelt.id = int.Parse(ds2.Tables[0].Rows[n]["id"].ToString());
                    }
                    if (ds2.Tables[0].Rows[n]["decla_id"].ToString() != "")
                    {
                        modelt.decla_id = ds2.Tables[0].Rows[n]["decla_id"].ToString();
                    }
                    if (ds2.Tables[0].Rows[n]["position"].ToString() != "")
                    {
                        modelt.position = ds2.Tables[0].Rows[n]["position"].ToString();
                    }
                    if (ds2.Tables[0].Rows[n]["storage_id"].ToString() != "")
                    {
                        modelt.storage_id = ds2.Tables[0].Rows[n]["storage_id"].ToString();
                    }
                    if (ds2.Tables[0].Rows[n]["shelf_id"].ToString() != "")
                    {
                        modelt.shelf_id = ds2.Tables[0].Rows[n]["shelf_id"].ToString();
                    }

                    if (ds2.Tables[0].Rows[n]["floor_id"].ToString() != "")
                    {
                        modelt.floor_id = ds2.Tables[0].Rows[n]["floor_id"].ToString();
                    }
                    if (ds2.Tables[0].Rows[n]["line_id"].ToString() != "")
                    {
                        modelt.line_id = ds2.Tables[0].Rows[n]["line_id"].ToString();
                    }
                    models.Add(modelt);
                    
                }
                model.dt_locations = models;              
                return model;
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,decla_id,position,storage_id,shelf_id,floor_id,line_id,is_exist ");
            strSql.Append(" FROM dt_location ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetLocation(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select dt_layer.layer_id as 层,");
            strSql.Append("MAX( case line_id when '01' then is_exist else 0 end ) '01',");
            strSql.Append("MAX( case line_id when '02' then is_exist else 0 end ) '02',");
            strSql.Append("MAX( case line_id when '03' then is_exist else 0 end ) '03',");
            strSql.Append("MAX( case line_id when '04' then is_exist else 0 end ) '04',");
            strSql.Append("MAX( case line_id when '05' then is_exist else 0 end ) '05',");
            strSql.Append("MAX( case line_id when '06' then is_exist else 0 end ) '06',");
            strSql.Append("MAX( case line_id when '07' then is_exist else 0 end ) '07',");
            strSql.Append("MAX( case line_id when '08' then is_exist else 0 end ) '08',");
            strSql.Append("MAX( case line_id when '09' then is_exist else 0 end ) '09',");
            strSql.Append("MAX( case line_id when '10' then is_exist else 0 end ) '10',");
            strSql.Append("MAX( case line_id when '11' then is_exist else 0 end ) '11',");
            strSql.Append("MAX( case line_id when '12' then is_exist else 0 end ) '12',");
            strSql.Append("MAX( case line_id when '13' then is_exist else 0 end ) '13',");
            strSql.Append("MAX( case line_id when '14' then is_exist else 0 end ) '14',");
            strSql.Append("MAX( case line_id when '15' then is_exist else 0 end ) '15',");
            strSql.Append("MAX( case line_id when '16' then is_exist else 0 end ) '16',");
            strSql.Append("MAX( case line_id when '17' then is_exist else 0 end ) '17',");
            strSql.Append("MAX( case line_id when '18' then is_exist else 0 end ) '18',");
            strSql.Append("MAX( case line_id when '19' then is_exist else 0 end ) '19',");
            strSql.Append("MAX( case line_id when '20' then is_exist else 0 end ) '20',");
            strSql.Append("MAX( case line_id when '21' then is_exist else 0 end ) '21',");
            strSql.Append("MAX( case line_id when '22' then is_exist else 0 end ) '22',");
            strSql.Append("MAX( case line_id when '23' then is_exist else 0 end ) '23',");
            strSql.Append("MAX( case line_id when '24' then is_exist else 0 end ) '24',");
            strSql.Append("MAX( case line_id when '25' then is_exist else 0 end ) '25' ");
            strSql.Append("from dt_layer left outer join dt_location on dt_layer.layer_id=dt_location.floor_id ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" group by dt_layer.layer_id ");
            strSql.Append(" order by dt_layer.layer_id desc ");
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion  BasicMethod
    }
}
